<?php
class ClassTermClass extends ListClass
{
    protected $TERM_OBJID;
    protected $CLASS_OBJID;
    
    function __construct($_URL = null, $_CONFIG_OBJ = null)
    {
        parent::__construct($_URL, $_CONFIG_OBJ);
    }

    function Load()
    {
        global $SMARTY,$DBobject;
        $template = "";
        $data = "";
        // Split the URL
        $url = ltrim(rtrim($this->URL, '/'), '/');
    
        // ------------- LOAD OPTIONS FOR SELECT INPUTS --------------
        //var_dump($this->CONFIG_OBJ->table->options->field);exit;
        foreach ($this->CONFIG_OBJ->table->options->field as $f) {
            $options = array();
            //var_dump($f->attributes()->recursive);exit;
            if ($f->attributes()->recursive) {
                $parentID = 0;
                if ($this->CONFIG_OBJ->root_parent_id) {
                    $parentID = $this->CONFIG_OBJ->root_parent_id;
                }
                $article_f ['options'] ["{$f->name}"] = $this->getOptionsCatTree($f, $parentID);
            } else {
                $pre = str_replace("tbl_", "", $f->table);
                $ref = array();
                foreach ($f->reference as $r) {
                    $ref[] = $r;
                }
                //var_dump($ref);//exit;
                $sql = "SELECT {$f->id},". implode(',', $ref)." FROM {$f->table} WHERE {$pre}_deleted IS NULL " . ($f->where != '' ? "AND {$f->where} " : "") . " " . ($f->orderby != '' ? " ORDER BY {$f->orderby} " : "");
                //var_dump($sql);exit;
                if ($res = $DBobject->wrappedSqlGet($sql)) {
                    foreach ($res as $key => $row) {
                        $value = array();
                        foreach ($ref as $r) {
                            $value[] = $row ["{$r}"];
                        }
                        $options[] = array (
                            'id' => $row ["{$f->id}"],
                            'value' => implode(', ', $value)
                        );
                    }
                }
            }
            //var_dump($options);exit;
            $SMARTY->assign("{$f->name}", $options);
        }
        
        foreach ($this->CONFIG_OBJ->subpage as $sub) {
            if (preg_match("/^{$this->CONFIG_OBJ->url}\/{$sub->url}/", $url)) {
                if ($sub->url == 'classterm') {
                    if ($classTermId = $this->ChkCache($url)) {
                        $classTermArr = $this->GetClassTermData($classTermId, false);
                        if (!empty($classTermArr)) {
                            foreach ($classTermArr as $key => $val) {
                                $SMARTY->assign($key, unclean($val));
                            }
                        
                            $SMARTY->assign("classterm_id", $classTermId);
                            $data = array();
                            $data[$classTermId]["title"] =  ucfirst(unclean($classTermArr['class_name'])) .' | '. ucfirst(unclean($classTermArr['term_name']));
                            $data[$classTermId]["url"] = $url;
                        
                            $bdata = parent::LoadBreadcrumb($this->CONFIG_OBJ->parentID, $data);
                            $SMARTY->assign("breadcrumbs", $bdata);
                    
                            $p_data = $this->LoadParents($this->CONFIG_OBJ->parentID);
                            $SMARTY->assign("listing_parent", $p_data);
                        
                            foreach ($sub->associated as $a) {
                                $t_data = $this->LoadAssociated($a, $classTermArr["{$a->linkfield}"]);
                                $SMARTY->assign("{$a->name}", $t_data);
                            }
                            /* $fdata = $this->GetFranchiseeData($classTermArr['location_franchisee_id']);
                            $SMARTY->assign("franchisees",$fdata); */
                            return $sub->template;
                        }
                    }
                } else {
                    $this->LoadClassesList($sub);
                    return $sub->template;
                }
            }
        }
        return parent::Load();
    }

  
  
    function LoadClassesList($_CONFIG, $franchiseArr = array(), $same_term = 0)
    {
        global $SMARTY,$DBobject;
    
        $location = '';
        $months  = 0;
        $classNum = 0;
        $surrounding = false;
     
        if (!empty($_REQUEST['surrounding'])) {
            $surrounding = true;
            $_SESSION['search']['surrounding'] = true;
        }
        if (!empty($_REQUEST['location'])) {
            $_SESSION['search']['location'] = $_REQUEST['location'];
            $location = $_REQUEST['location'];
            if (is_numeric($location)) {
                $SMARTY->assign("search_postcode", $location);
                try {
                    $sql = "SELECT DISTINCT(CONCAT(postcode_suburb,', ',postcode_state,', ',postcode_postcode)) AS VALUE FROM tbl_postcode WHERE postcode_deleted IS NULL AND postcode_postcode LIKE :pcode LIMIT 4";
                    $param = array(":pcode"=>"".substr($location, 0, 3)."%");
                    $pcres = $DBobject->executeSQL($sql, $param);
                    $SMARTY->assign("search_postcode_alts", $pcres);
                } catch (Exception $e) {
                }
            } else {
                $SMARTY->assign("location", $location);
                try {
                    $sql = "SELECT DISTINCT(CONCAT(postcode_suburb,', ',postcode_state,', ',postcode_postcode)) AS VALUE FROM tbl_postcode WHERE postcode_deleted IS NULL AND MATCH(postcode_suburb) AGAINST(:scode) LIMIT 4";
                    $param = array(":scode"=>"{$location}");
                    $pcres = $DBobject->executeSQL($sql, $param);
                    if (empty($pcres)) {
                        $sql = "SELECT DISTINCT(CONCAT(postcode_suburb,', ',postcode_state,', ',postcode_postcode)) AS VALUE FROM tbl_postcode WHERE postcode_deleted IS NULL AND postcode_suburb LIKE :scode LIMIT 4";
                        $param = array(":scode"=>"{$location}%");
                        $pcres = $DBobject->executeSQL($sql, $param);
                        if (empty($pcres)) {
                            $sql = "SELECT DISTINCT(CONCAT(postcode_suburb,', ',postcode_state,', ',postcode_postcode)) AS VALUE FROM tbl_postcode WHERE postcode_deleted IS NULL AND postcode_suburb LIKE :scode LIMIT 4";
                            $param = array(":scode"=>"%{$location}%");
                            $pcres = $DBobject->executeSQL($sql, $param);
                        }
                    }
                    $SMARTY->assign("search_postcode_alts", $pcres);
                } catch (Exception $e) {
                }
            }
        }

        if (!empty($_REQUEST['state']) && !empty($_REQUEST['suburb']) && !empty($_REQUEST['country'])) {
            $state = $_SESSION['search']['state'] = $_REQUEST['state'];
            $suburb = $_SESSION['search']['suburb'] = $_REQUEST['suburb'];
            $country = $_SESSION['search']['country'] = $_REQUEST['country'];
            $SMARTY->assign("search_state", $state);
            $SMARTY->assign("search_suburb", $suburb);
            $SMARTY->assign("search_country", $country);
            try {
                $sql = "SELECT region_name FROM tbl_region WHERE region_id IN(:rid1,:rid2,:rid3)";
                $param = [":rid1"=>$state, ":rid2"=>$suburb, ":rid3"=>$country];
                $pcres = $DBobject->executeSQL($sql, $param);
                if (is_array($pcres)) {
                    foreach ($pcres as $key => $value) {
                        $location .= $value['region_name'].', ';
                    }
                    $location = rtrim($location, ', ');
                }
                $SMARTY->assign("search_postcode_alts", $pcres);
            } catch (Exception $e) {
            }
        }

        if (!empty($_REQUEST['months'])) {
            //课程类型 年龄段选择
            $_SESSION['search']['months'] = intval($_REQUEST['months']);
            $months = intval($_REQUEST['months']);
        }
        if (!empty($_REQUEST['class'])) {
            //课程类型 班级选择
            $_SESSION['search']['class'] = intval($_REQUEST['class']);
            $classNum = intval($_REQUEST['class']);
        }

        $locObjId = empty($_REQUEST['id'])?0:$_REQUEST['id'];
        if (!empty($location) || !empty($locObjId)) {
            $terms = $this->GetCurrentTerm(0, $location, $locObjId, $same_term);
            //var_dump($terms);exit;
            $classes = $this->getCurrentClassesGroupByLocations(0, $locObjId, $months, $location, $surrounding, $franchiseArr, $classNum);
            //var_dump($classes);exit;
        }
        $SMARTY->assign("location", $location);
        $SMARTY->assign("search", $_SESSION['search']);
        $SMARTY->assign("terms", $terms);
        $SMARTY->assign("classes", $classes);
    
    
        // ------------- LOAD OPTIONS FOR SELECT INPUTS --------------
        foreach ($_CONFIG->options->field as $f) {
            $options = array();
            if ($f->attributes()->recursive) {
                $parentID = 0;
                if ($this->CONFIG_OBJ->root_parent_id) {
                    $parentID = $this->CONFIG_OBJ->root_parent_id;
                }
                $options = $this->getOptionsCatTree($f, $parentID);
            } else {
                $pre = str_replace("tbl_", "", $f->table);
                $ref = array();
                foreach ($f->reference as $r) {
                    $ref[] = $r;
                }
                $sql = "SELECT {$f->id},". implode(',', $ref)." FROM {$f->table} WHERE {$pre}_deleted IS NULL " . ($f->where != '' ? "AND {$f->where} " : "") . " " . ($f->orderby != '' ? " ORDER BY {$f->orderby} " : "");
                if ($res = $DBobject->wrappedSqlGet($sql)) {
                    foreach ($res as $key => $row) {
                        $value = array();
                        foreach ($ref as $r) {
                            $value[] = $row ["{$r}"];
                        }
                        $options[] = array (
                            'id' => $row ["{$f->id}"],
                            'value' => implode(', ', $value)
                        );
                    }
                }
            }
              $SMARTY->assign("{$f->name}", $options);
        }
    }


  
    function ChkCache($_url)
    {
        global $SMARTY,$DBobject;
        $args = explode('/', $_url);
        $cKey = count($args) - 1;
        $classUrl = $args[$cKey];
        $termUrl = $args[$cKey - 1];
    
        $sql = "SELECT cache_record_id FROM cache_tbl_classterm WHERE cache_url = :url AND EXISTS (SELECT classterm_id, term_object_id, class_object_id  FROM tbl_classterm LEFT JOIN tbl_term ON term_id = classterm_term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id 
    		WHERE term_published = '1' AND term_deleted IS NULL AND class_published = '1' AND class_deleted IS NULL AND classterm_id = cache_record_id)";
        $params = array(
        ":url"=>$_url
        );
        $row = $DBobject->wrappedSqlGetSingle($sql, $params);
        if (empty($row)) {
              $sql2 = "SELECT classterm_id, term_object_id, class_object_id  FROM tbl_classterm LEFT JOIN tbl_term ON term_id = classterm_term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id 
    		WHERE term_published = '1' AND term_deleted IS NULL AND class_published = '1' AND class_deleted IS NULL AND term_url = :term_url AND class_url = :class_url";
              $params2 = array(
                  ":term_url"=>$termUrl,
                ":class_url"=>$classUrl
                );
            if ($res = $DBobject->wrappedSqlGet($sql2, $params2)) {
                    $p_data = $this->LoadParents($this->CONFIG_OBJ->parentID);
                    $prependUrl = empty($p_data['listing_url'])?'classterm/':$p_data['listing_url'].'/classterm/';
                  self::BuildCache($prependUrl);
                  $row = $DBobject->wrappedSqlGetSingle($sql, $params);
            }
        }
        if (! empty($row)) {
              return $row['cache_record_id'];
        }
        return false;
    }

    function BuildCache($_prependUrl)
    {
        global $DBobject;

        $sql = "TRUNCATE cache_tbl_classterm;";
        $DBobject->wrappedSql($sql);
   
        $sql2 = "INSERT INTO cache_tbl_classterm (cache_record_id,cache_published,cache_url,cache_modified) VALUES  ( :id, :published, :url, now() ) ";
    
        $sql = "SELECT classterm_id, term_url, class_url  FROM tbl_classterm LEFT JOIN tbl_term ON term_id = classterm_term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id
    		WHERE term_published = '1' AND term_deleted IS NULL AND class_published = '1' AND class_deleted IS NULL";
    
        if ($res = $DBobject->wrappedSql($sql)) {
            foreach ($res as $row) {
                $params = array(
                  ":id"=>$row['classterm_id'],
                  ":published"=>'1',
                  ":url"=>$_prependUrl . $row['term_url'] . '/' . $row['class_url']
                );
                $DBobject->wrappedSql($sql2, $params);
            }
        }
    }

  

  /**
   * Return term recordset based of the current date.
   * If term_object_id is given, then ignore the current date.
   *
   * @param number $_term_ObjId
   * @return array
   */
    function GetCurrentTerm($_termObjId = 0, $_location = '', $_locationId = '', $_sameTerm = 0)
    {
        global $DBobject;
  
        $params = array();
        if (!empty($_termObjId)) {
            $sql = "SELECT * FROM tbl_term WHERE term_object_id = :id AND term_deleted IS NULL AND term_published = 1 AND term_region = :region";
            $params = array(":id" => $_termObjId);
        } else {
            $state = '';
            if (!empty($_locationId)) {
                $sql = "SELECT location_state FROM tbl_location WHERE location_deleted IS NULL AND location_listing_id = '9' AND location_object_id = :location_object_id";
                if ($res = $DBobject->wrappedSql($sql, array("location_object_id"=>$_locationId))) {
                    $state = $res[0]['location_state'];
                }
            } elseif (!empty($_location)) {
                // $sql = "SELECT postcode_state FROM tbl_postcode WHERE postcode_deleted IS NULL AND ( CONCAT(postcode_suburb,', ',postcode_state,', ',postcode_postcode) like :suburb OR postcode_postcode = :postcode )";
                // if ($res = $DBobject->wrappedSql($sql, array("suburb"=>"$_location", ":postcode"=>$_location))) {
                //     $state = $res[0]['postcode_state'];
                // }
                list($state,$suburb,$country) = explode(',', $_location);
            }
            if (!empty($state)) {
                $where = "AND term_region = :region";
                $params = [":region" => trim($state)];
            }
            if (!empty($_sameTerm)) {
                $sameTermSQL = " AND CONCAT(term_termcategory_id, DATE_FORMAT(term_start_date,'%Y')) IN (SELECT DISTINCT(CONCAT(s.term_termcategory_id, DATE_FORMAT(s.term_start_date,'%Y'))) FROM tbl_term AS s WHERE s.term_deleted IS NULL AND s.term_published = 1 AND s.term_object_id = :same_term ) ";
                $params["same_term"] = $_sameTerm;
            }
            $sql = "SELECT * FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND CURDATE() <= term_end_date {$where}{$sameTermSQL} ORDER BY term_start_date";
        }
        $res = $DBobject->wrappedSql($sql, $params);
        if ($res) {
            $result = array();
            foreach ($res as $r) {
                $result["{$r['term_id']}"] = $r;
                $sql = "SELECT classterm_id, class_object_id FROM tbl_classterm LEFT JOIN tbl_class ON class_object_id = classterm_class_id WHERE classterm_deleted IS NULL AND class_deleted IS NULL AND class_published = '1' AND classterm_term_id = :classterm_term_id";
                $params = array(":classterm_term_id" => $r['term_id']);
                $result["{$r['term_id']}"]['classes'] = $DBobject->wrappedSql($sql, $params);
            }
            return $result;
        }
        return false;
    }
  
  
  
   /**
    * Return class recordset given the class_object_id
    *
    * @param int $_classObjId
    * @return array
    */
    public function getClassDataByObjId($_classObjId)
    {
        global $DBobject;

        $sql = "SELECT tbl_class.*, 
        tbl_location.*, 
        tbl_agegroup.agegroup_name, 
        tbl_agegroup.agegroup_shortname, 
        tbl_agegroup.agegroup_min, 
        tbl_agegroup.agegroup_max, 
        tbl_admin.admin_name, 
        tbl_admin.admin_surname, 
        tbl_admin.admin_email 
        FROM tbl_class
        LEFT JOIN tbl_location ON location_object_id = class_location_id
        LEFT JOIN tbl_admin ON admin_id = class_admin_id
        LEFT JOIN tbl_agegroup ON agegroup_id = class_agegroup_id
        WHERE class_deleted IS NULL AND class_published = '1' AND class_object_id = :id AND location_published = '1' AND location_deleted IS NULL AND admin_deleted IS NULL AND agegroup_deleted IS NULL";
        $res = $DBobject->wrappedSql($sql, array (":id" => $_classObjId));
        if ($res[0]) {
            $sql = "SELECT * FROM tbl_classtime WHERE classtime_class_id = :class_id ORDER BY classtime_start_time";
            $classtimes = $DBobject->wrappedSql($sql, [':class_id'=> $res[0]['class_id']]);
            $res[0]['classtimes'] = $classtimes;
        }
        return $res[0];
    }
  
  
  
  /**
   * Return term recordset given the term_object_id. Optionally week info (count and current) could be included.
   *
   * @param int $_termObjId
   * @param bool $displayWeekInfo
   * @return array
   */
    function GetTermDataByObjId($_termObjId, $displayWeekInfo = false)
    {
        global $DBobject;
  
        $result = array();
        $sql = "SELECT * FROM tbl_term WHERE term_deleted IS NULL AND term_published = '1' AND term_object_id = :id";
        if ($res = $DBobject->wrappedSql($sql, array (":id" => $_termObjId))) {
            if ($displayWeekInfo) {
                $curWeek = 0;
                if (intval(dateDifference($res[0]['term_end_date'], date())) < 0) {
                    $curWeek = $this->GetCurrentWeekByTermStartDate($res[0]['term_start_date']);
                }
                $weeks = $this->GetCurrentWeekByTermStartDate($res[0]['term_start_date'], $res[0]['term_end_date']);
                $result= array_merge($res[0], array('weeks' => $weeks, 'current_week' => $curWeek));
            } else {
                $result = $res[0];
            }
        }
        return $result;
    }
  
  /**
   * Return all term recordset. Optionally week info (count and current) could be included.
   *
   * @param int $_termObjId
   * @param bool $displayWeekInfo
   * @param bool $_onlyCurrent
   * @return array
   */
    function GetAllTermsData($_order = null, $displayWeekInfo = false, $_onlyCurrent = false, $_franchiseeIdArr = array())
    {
        global $DBobject;
    
        $result = array();
        $order = empty($_order)?"term_start_date DESC":$_order;
        $sqlFilter = '';
        if (!empty($_franchiseeIdArr)) {
            $tempArr = array();
            foreach ($_franchiseeIdArr as $fid) {
                $tempArr[] = "location_franchisee_id = '$fid'";
            }
            $sqlFilter = " AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_published = 1 AND classterm_deleted IS NULL AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL AND (" . implode(' OR ', $tempArr) . ") ) )";
        }
        $where = $_onlyCurrent?"AND CURDATE() BETWEEN term_start_date AND term_end_date ":"";
        $sql = "SELECT * FROM tbl_term WHERE term_deleted IS NULL AND term_published = '1' $where $sqlFilter ORDER BY {$order}";
        if ($res = $DBobject->wrappedSql($sql)) {
            if ($displayWeekInfo) {
                foreach ($res as $r) {
                    $curWeek = 0;
                    if (intval(dateDifference($r['term_end_date'], date())) < 0) {
                        $curWeek = $this->GetCurrentWeekByTermStartDate($r['term_start_date']);
                    }
                    $weeks = $this->GetCurrentWeekByTermStartDate($r['term_start_date'], $r['term_end_date']);
                    $result[] = array_merge($r, array('weeks' => $weeks, 'current_week' => $curWeek));
                }
            } else {
                $result = $res;
            }
        }
        return $result;
    }
  
  
  
  
  /**
   * Return current week given the term_start_date
   * Optional:
   * $_notToday - Another day to set comparison.  Default null (for today)
   *
   * @param string $_termStartDate
   * @param string $_notToday
   * @return int
   */
    function GetCurrentWeekByTermStartDate($_termStartDate, $_notToday = null)
    {
        //获取这一天的周一的日期
        $_termStartDate = date("Y-m-d", strtotime("$_termStartDate Monday"));
        $weeks = getWeeksDateDifference($_termStartDate, $_notToday);
        if (is_int($weeks)) {
            $result = ($weeks > 0)?$weeks+1:1;
        } else {
            $result = (ceil($weeks) > 0)?ceil($weeks):1;
        }
        return $result;
    }
  
  
  /**
   * Return true when date is holiday
   *
   * @param string $_date
   * @param string $_location
   * @return array
   */
    function IsHoliday($_date, $_location)
    {
        global $DBobject;
  
        $sql = "SELECT holiday_state FROM tbl_holiday WHERE holiday_deleted IS NULL AND holiday_date = :holiday_date ";
        if ($res = $DBobject->wrappedSql($sql, array (":holiday_date" => $_date))) {
            foreach ($res as $hd) {
                if (empty($hd['holiday_state']) || $hd['holiday_state'] == $_location) {
                    return true;
                }
            }
        }
        return false;
    }
  
  
  
  /**
   * Return array with weeks number for a classterm given the term_start_date, term_end_date, class_weekday and location_state
   *
   * @param date $_termStartDate
   * @param date $_termEndDate
   * @param string $_classWeekday
   * @param string $_classLocationState
   * @return array
   */
    function GetClassSessions($_termStartDate, $_termEndDate, $_classWeekday, $_classLocationState, $inclToday = true)
    {
        global $DBobject;
  
        $result = array();
            

        if ($inclToday) {
            $startDate = (strtotime(date("Y-m-d")) > strtotime($_termStartDate))?date("Y-m-d"):$_termStartDate ;
        } else {
            $startDate = (strtotime(date("Y-m-d", strtotime("+1 day"))) > strtotime($_termStartDate))?date("Y-m-d", strtotime("+1 day")):$_termStartDate ;
        }

        $sessions = getWeekdayDates($_classWeekday, $startDate, $_termEndDate);
        //var_dump($sessions);exit;
        foreach ($sessions as $ss) {
            if (!$this->IsHoliday($ss, $_classLocationState)) {
                $result['weeks'][] = $this->GetCurrentWeekByTermStartDate($_termStartDate, $ss);
                $result['dates'][] = $ss;
            }
        }
        return $result;
    }
  
  

  
  /**
   * NOT BEING USED!
   * Return asociative array with individual/session class availability (size, taken and available) given the term_object_id, class_object_id, term week and class_size - IGNORING TRIALS.
   *
   * @param int $_termObjId
   * @param int $_classObjId
   * @param int $_week
   * @param int $_classSize
   */
    function GetClassSessionAvailability($_termObjId, $_classObjId, $_week, $_classSize)
    {
        /* ============== NOT BEING USED ============ */
        global $DBobject;
  
        $result = array();
        $params = array (":termObjId" => $_termObjId, ":classObjId" => $_classObjId, ":week" => $_week);

        $n=0;
        $where="";
        foreach ($_SESSION['students'] as $std) {
            $n++;
            $where .= " AND enrolment_student_id != :stid{$n} ";
            $params[":stid{$n}"] = $std['student_id'];
        }
        // TRIALS ARE NOT INCLUDED
        $sql = "SELECT DISTINCT(enrolment_student_id) FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_type != 'T' AND enrolment_term_object_id = :termObjId AND enrolment_class_object_id = :classObjId AND enrolment_week = :week {$where}";

        $taken=0;
        $students=array();
        if ($res = $DBobject->wrappedSql($sql, $params)) {
             $taken = count($res);
             $students = $res;
        }
        $result['size'] = $_classSize;
        $result['taken'] = $taken;
        $result['available'] = $_classSize - $taken;
        $result['students'] = $students;
        return $result;
    }
  
  
  
  /**
   * Return asociative array with class availability (size, taken and available) given the classterm_id.
   *
   * @param int $_classTermId
   * @return array
   */
    public function getClasssAvailability($_termDataArr, $_classDataArr, $inclToday = true)
    {
        global $DBobject;
  
        $result = array();
        $taken = array();
        $takenTrial = 0;
        $n=0;
        $where = '';
        $params = array (":termObjId" => $_termDataArr['term_object_id'], ":classObjId" => $_classDataArr['class_object_id']);
     
        //Get all weeks and dates
        $class_weekday = isset($_classDataArr['classtimes'])?$_classDataArr['classtimes'][0]['classtime_weekday']:$_classDataArr['class_weekday'];
        $sessions = $this->GetClassSessions($_termDataArr['term_start_date'], $_termDataArr['term_end_date'], $class_weekday, $_classDataArr['location_state'], $inclToday);
        $curWeek = $sessions['weeks'][0];
    
        if (empty($sessions)) {
            //CHANGE - Add total trials to taken amount
            $result['size'] = 0;
            $result['taken'] = 0;
            $result['available'] = 0;
            $result['weeks'] = $sessions['weeks'];
            $result['dates'] = $sessions['dates'];
            return $result;
        }

        //Set current week as last week if current week is greater than 1 and class_weekday has less than three days difference
        if ($curWeek > 1) {
            $classDate = getDateByWeekdayStartDate($class_weekday, $_termDataArr['term_start_date'], $curWeek-2);
            $dateDiff = intval(dateDifference($classDate, date('Y-m-d'), '%R%a'));
            if ($dateDiff < 3 && $dateDiff >= 0) {
                $curWeek = $curWeek-1;
            }
        }
        //Calculate total number of trials for this class in this term which are still active and are for classes > 3 days ago
        $where1 = '';
        $params2 = array();
    
        $_tmp_students = $_SESSION['students'];
        if (!empty($_SESSION['enrol']['students'])) {
            $_tmp_students = $_SESSION['enrol']['students'];
        }
        foreach ($_tmp_students as $std) {
            $n++;
            $where1 .= " AND enrolment_student_id != :stid{$n} ";
            $params2[":stid{$n}"] = $std['student_id'];
        }
        $sql = "SELECT DISTINCT(enrolment_student_id) FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_type = 'T' AND enrolment_published = '1' AND enrolment_term_object_id = :termObjId AND enrolment_class_object_id = :classObjId AND enrolment_week >= :week {$where1}";
        if ($res = $DBobject->wrappedSql($sql, array_merge($params, $params2, array(":week" => $curWeek)))) {
            $takenTrial = count($res);
            foreach ($res as $std) {
                $n++;
                $where .= " AND enrolment_student_id != :stid{$n}";
                $params[":stid{$n}"] = $std['enrolment_student_id'];
            }
        }
        //Calculate class capacity per week/session
        foreach ($sessions['weeks'] as $key => $wk) {
            //Class capacity excluding trials and duplicated student_ids
            $sql = "SELECT COUNT(DISTINCT(enrolment_student_id)) AS taken FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_type != 'T' AND enrolment_term_object_id = :termObjId AND enrolment_class_object_id = :classObjId AND enrolment_week = :week {$where}";
            if ($res = $DBobject->wrappedSql($sql, array_merge($params, array(":week" => $wk)))) {
                $taken[] = $res[0]['taken'];
            }
        }
        //CHANGE - Add total trials to taken amount
        $result['size'] = intval($_classDataArr['class_size']);
        $result['taken'] = intval(max($taken)) + $takenTrial;
        $result['available'] = intval($result['size']) - intval($result['taken']);
        $result['weeks'] = $sessions['weeks'];
        $result['dates'] = $sessions['dates'];
        return $result;
    }
  
  
  
  /**
   * Return array with classes of the current term(s)
   * Optional:
   * $_termObjId - Term object id. Default 0 for all current terms.
   *
   * @param int $_termObjId
   * @param int $_age
   * @param int $_location
   * @return array
   */
    function GetCurrentClasses($_termObjId = 0, $_age = 0, $_location = null)
    {
        global $DBobject;
  
        $additionalArr = array();
        $additionalStr = '';
        if (!empty($_age)) {
             $additionalStr = 'AND agegroup_min <= :age AND agegroup_max >= :age';
             $additionalArr = array(":age" => $_age);
        }
        if (!empty($_location)) {
            $additionalStr .= ' AND (location_suburb LIKE :suburb OR location_postcode = :postcode )';
            $additionalArr = array_merge($additionalArr, array(":suburb" => "%$_location%", ":postcode" => $_location));
        }
    
        $result = array();
        $terms = $this->GetCurrentTerm($_termObjId);
        $sql = "SELECT tbl_classterm.*, class_object_id, cache_url, tbl_class.*, tbl_location.*, tbl_agegroup.agegroup_name, tbl_agegroup.agegroup_shortname, tbl_agegroup.agegroup_min, tbl_agegroup.agegroup_max, tbl_admin.admin_name, tbl_admin.admin_surname, tbl_admin.admin_email FROM tbl_classterm 
  			LEFT JOIN tbl_class ON class_object_id = classterm_class_id 
  			LEFT JOIN cache_tbl_classterm ON cache_record_id = classterm_id 
  			LEFT JOIN tbl_location ON location_object_id = class_location_id
		  	LEFT JOIN tbl_admin ON admin_id = class_admin_id
		  	LEFT JOIN tbl_agegroup ON agegroup_id = class_agegroup_id
  			WHERE classterm_deleted IS NULL AND classterm_published = '1' AND classterm_term_id = :id AND class_deleted IS NULL AND class_published = '1'
  			AND location_published = '1' AND location_deleted IS NULL AND admin_deleted IS NULL AND agegroup_deleted IS NULL $additionalStr";
        foreach ($terms as $tm) {
            $param = array_merge($additionalArr, array (":id" =>$tm['term_id']));
            if ($res = $DBobject->wrappedSql($sql, $param)) {
                foreach ($res as $r) {
                    $result["{$r['classterm_id']}"] = array_merge($tm, $r);
                    $result["{$r['classterm_id']}"]['availability'] = $this->GetClasssAvailability($tm, $r);
                    $result["{$r['classterm_id']}"]['url'] = '/'. $r['cache_url'];
                }
            }
        }
        return $result;
    }
  
    //通过地区获取课程
    protected function getCurrentClassesGroupByLocations($_termObjId = 0, $_locationObjId = 0, $_age = 0, $_location = null, $_near = true, $_franchiseArr = [], $_classNum = 0)
    {
        global $DBobject;
    
        $additionalArr = array();
        $additionalStr = '';
        if (!empty($_franchiseArr)) {
            $sqlArr = array();
            foreach ($_franchiseArr as $f) {
                $sqlArr[] = " location_franchisee_id = '{$f}' ";
            }
            $additionalStr .= " AND (". implode(' OR ', $sqlArr).") ";
        }
        if (!empty($_age)) {
            $additionalStr .= ' AND agegroup_min <= :age AND agegroup_max >= :age';
            $additionalArr['age'] = $_age;
        }
        if (!empty($_classNum)) {
            $additionalStr .= ' AND class_num = :classnum';
            $additionalArr['classnum'] = $_classNum;
        }
    
        $exactLocArr = array();
        $exactLocStr = '';
        if (!empty($_locationObjId)) {
            $exactLocStr = " AND location_object_id = :location_object_id";
            $exactLocArr['location_object_id'] = $_locationObjId;
        } elseif (!empty($_location)) {
            list($state,$suburb,$country) = explode(',', $_location);
            if ($_near) {
                $nearlocations = GetNearPostcodes($_location, 35, 10);
            }
            $exactLocStr = " AND location_state = :state AND location_suburb = :suburb AND location_country = :country";
            $exactLocArr = ['state' => trim($state), 'suburb'=>trim($suburb), 'country'=>trim($country)];
        }

        $result = array();
        $terms = $this->GetCurrentTerm($_termObjId, $_location, $_locationObjId);
        $classTermSql = "SELECT tbl_classterm.*, 
        class_object_id, 
        cache_url, 
        tbl_class.*, 
        tbl_classtime.classtime_weekday AS class_weekday, 
        tbl_classtime.classtime_start_time AS class_start_time, 
        tbl_classtime.classtime_end_time AS class_end_time, 
        tbl_location.*, 
        tbl_agegroup.agegroup_name, 
        tbl_agegroup.agegroup_shortname, 
        tbl_agegroup.agegroup_min, 
        tbl_agegroup.agegroup_max, 
        tbl_admin.admin_name, 
        tbl_admin.admin_surname, 
        tbl_admin.admin_email 
        FROM tbl_classterm 
        LEFT JOIN tbl_class ON class_object_id = classterm_class_id
        LEFT JOIN tbl_classtime ON class_id = classtime_class_id 
        LEFT JOIN cache_tbl_classterm ON cache_record_id = classterm_id
        LEFT JOIN tbl_location ON location_object_id = class_location_id
        LEFT JOIN tbl_admin ON admin_id = class_admin_id
        LEFT JOIN tbl_agegroup ON agegroup_id = class_agegroup_id
        WHERE classterm_deleted IS NULL AND classterm_published = '1' AND classterm_term_id = :id AND class_deleted IS NULL AND class_published = '1' AND location_published = '1' AND location_deleted IS NULL AND admin_deleted IS NULL AND agegroup_deleted IS NULL $additionalStr ";
        $classTermOrderSql = " ORDER BY location_name, classtime_start_time";
        foreach ($terms as $tm) {
            //Get exact location
            $param = array_merge($additionalArr, array_merge($exactLocArr, array ("id" =>$tm['term_id'])));
            //var_dump($param);
            //var_dump($classTermSql . $exactLocStr . $classTermOrderSql);
            $res = $DBobject->wrappedSql($classTermSql . $exactLocStr . $classTermOrderSql, $param);
            if ($res) {
                foreach ($res as $r) {
                    $availability = $this->GetClasssAvailability($tm, $r);
                    //var_dump($availability);
                    if (!empty($availability['size'])) {
                        $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]['location_name'] = $r['location_name'];
                        $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]['location_suburb_state'] = $r['location_suburb'] ." ". $r['location_country'];
                        $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]['location_address'] = $r['location_street'];
                        $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]["{$r['class_weekday']}"]["{$r['classterm_id']}"] = array_merge($tm, $r);
                        $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]["{$r['class_weekday']}"]["{$r['classterm_id']}"]['availability'] = $availability;
                        $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]["{$r['class_weekday']}"]["{$r['classterm_id']}"]['url'] = '/'. $r['cache_url'];
                    }
                }
            }
        
            //Get near locations
            if (!empty($nearlocations)) {
                //if($_SERVER['REMOTE_ADDR']=='150.101.230.130') die(var_dump($nearlocations));
                foreach ($nearlocations as $val) {
                    $param = array_merge($additionalArr, array (":id" =>$tm['term_id'], ":location_object_id"=>$val));
                    if ($res = $DBobject->wrappedSql($classTermSql . " AND location_object_id = :location_object_id" . $classTermOrderSql, $param)) {
                        foreach ($res as $r) {
                            $availability = $this->GetClasssAvailability($tm, $r);
                            if (!empty($availability['size'])) {
                                $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]['location_name'] = $r['location_name'];
                                $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]['location_suburb_state'] = $r['location_suburb'] ." ". $r['location_country'];
                                $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]['location_address'] = $r['location_street'];
                                $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]["{$r['class_weekday']}"]["{$r['classterm_id']}"] = array_merge($tm, $r);
                                $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]["{$r['class_weekday']}"]["{$r['classterm_id']}"]['availability'] = $availability;
                                $result["{$tm['term_object_id']}"]["{$r['location_object_id']}"]["{$r['class_weekday']}"]["{$r['classterm_id']}"]['url'] = '/'. $r['cache_url'];
                            }
                        }
                    }
                }
            }
        }
        return $result;
    }
  
  
  /**
   * Return array with class-term details given the classterm_id
   *
   * @param int $_classTermId
   * @return array
   */
    function GetClassTermData($_classTermId, $inclToday = true)
    {
        global $DBobject;
  
        $result = array();
        $sql = "SELECT tbl_classterm.*, 
        term_object_id, 
        class_object_id, 
        cache_url 
        FROM tbl_classterm 
        LEFT JOIN tbl_term ON term_id = classterm_term_id 
        LEFT JOIN tbl_class ON class_object_id = classterm_class_id 
        LEFT JOIN cache_tbl_classterm ON cache_record_id = classterm_id 
        WHERE classterm_deleted IS NULL AND classterm_published = '1' AND classterm_id = :id AND class_deleted IS NULL AND class_published = '1' AND term_deleted IS NULL AND term_published = '1'";
        $params = [":id" => $_classTermId];
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            $classData = $this->getClassDataByObjId($res[0]['class_object_id']);
            $termData = $this->GetTermDataByObjId($res[0]['term_object_id']);
            $result = array_merge(array_merge($res[0], $classData), $termData);
            $result = array_merge($result, array('availability' => $this->getClasssAvailability($termData, $classData, $inclToday), "url"=>'/'.$res[0]['cache_url']));
        }
        return $result;
    }
  
  
  /**
   * Return array with class-term details given the classterm_id
   *
   * @param int $_classTermId
   * @return array
   */
    function GetClassTermDataByObjIds($_termObjId, $_classObjId, $inclToday = true)
    {
        global $DBobject;
  
        $result = array();
        $sql = "SELECT tbl_classterm.*, term_object_id, class_object_id, cache_url  FROM tbl_classterm LEFT JOIN tbl_term ON term_id = classterm_term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id LEFT JOIN cache_tbl_classterm ON cache_record_id = classterm_id
  			WHERE classterm_deleted IS NULL AND classterm_published = '1' AND class_object_id = :class_object_id AND term_object_id = :term_object_id AND class_deleted IS NULL AND class_published = '1' AND term_deleted IS NULL AND term_published = '1'";
        $params = array (":term_object_id" => $_termObjId, ":class_object_id" => $_classObjId);
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            $classData = $this->getClassDataByObjId($res[0]['class_object_id']);
            $termData = $this->GetTermDataByObjId($res[0]['term_object_id']);
            $result = array_merge(array_merge($res[0], $classData), $termData);
            $result = array_merge($result, array('availability' => $this->getClasssAvailability($termData, $classData, $inclToday), "url"=>'/'.$res[0]['cache_url']));
        }
        return $result;
    }
  
  
  /**
   * Return associative array with classes given the term_object_id.
   * Location => Weekday => Time/Age group (class record)
   * Optional: $_franchiseeIdArr for displaying only those under a given franchise
   *    Filter classes by instructor id ($_instructorId => class_admin_id)
   *
   * @param int $_termObjId
   * @param $_franchiseeIdArr
   * @param $_instructorId
   * @return array
   */
    function GetClassesListByTermObjId($_termObjId, $_franchiseeIdArr = array(), $_instructorId = 0)
    {
        global $DBobject;
    
        $sqlAddTb = '';
        $sqlAdd = '';
        $params = array (":term_object_id" => $_termObjId);
        if (!empty($_franchiseeIdArr)) {
            $tempArr = array();
            foreach ($_franchiseeIdArr as $fid) {
                $tempArr[] = "location_franchisee_id = '$fid'";
            }
            $sqlAdd = ' AND (' . implode(' OR ', $tempArr) . ') ';
        }
        if (!empty($_instructorId)) {
            $sqlAddTb = "LEFT JOIN tbl_class_instructor ON class_instructor_class_id = class_id";
            $sqlAdd .= "AND class_instructor_deleted IS NULL AND class_instructor_flag = 1 AND class_instructor_admin_id = :instructor";
            $params[":instructor"] = $_instructorId;
        }
        $result = array();
        $sql = "SELECT tbl_classterm.*, tbl_term.*, tbl_class.*, tbl_location.*, tbl_agegroup.agegroup_name, tbl_agegroup.agegroup_shortname FROM tbl_classterm LEFT JOIN tbl_term ON term_id = classterm_term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id LEFT JOIN tbl_location ON location_object_id = class_location_id LEFT JOIN tbl_agegroup ON agegroup_id = class_agegroup_id {$sqlAddTb} WHERE classterm_deleted IS NULL AND classterm_published = '1' AND term_object_id = :term_object_id AND class_deleted IS NULL AND class_published = '1' AND term_deleted IS NULL AND term_published = '1' AND location_published = '1' AND location_deleted IS NULL AND agegroup_deleted IS NULL {$sqlAdd} ORDER BY location_state, location_suburb, FIELD(class_weekday,'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'), class_start_time";
        //var_dump($sql);
        //var_dump($params);exit;
        if ($res = $DBobject->wrappedSql($sql, $params)) {
            foreach ($res as $r) {
                $result["{$r['location_object_id']}"]["location_name"] = $r['location_name'];
                $result["{$r['location_object_id']}"]["weekdays"]["{$r['class_weekday']}"]["{$r['class_object_id']}"] = $r;
            }
        }
        return $result;
    }
  /**
   * Return array with class_object_id and term_object_id given the classterm_id
   *
   * @param int $_classTermId
   * @return array
   */
    function GetClassTermObjIDs($_classTermId)
    {
        global $DBobject;
  
        $sql = "SELECT classterm_id, term_object_id, class_object_id  FROM tbl_classterm LEFT JOIN tbl_term ON term_id = classterm_term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id 
  			WHERE classterm_deleted IS NULL AND classterm_published = '1' AND classterm_id = :id AND class_deleted IS NULL AND class_published = '1' AND term_deleted IS NULL AND term_published = '1'";
        $params = array (":id" => $_classTermId);
        $res = $DBobject->wrappedSql($sql, $params);
        return $res[0];
    }
  
  
  
  /**
   * Return term_object_id of the previous term
   *
   */
    function GetPreviousTermObjId($_termRegion)
    {
        global $DBobject;
    
        $sql = "SELECT term_object_id FROM tbl_term WHERE term_end_date <= CURDATE() AND term_published = '1' AND term_region = :term_region AND term_deleted IS NULL ORDER BY term_end_date DESC, term_start_date DESC";
        $res = $DBobject->wrappedSql($sql, array(":term_region"=>$_termRegion));
        return $res[0]['term_object_id'];
    }
  
  
  
  /**
   * Return array with Franchisee details give the id
   *
   * @param int $_franchiseObjId
   * @return array
   */
    function GetFranchiseeData($_franchiseObjId)
    {
        global $DBobject;
  
        $sql = "SELECT tbl_franchisee.*, tbl_admin.* FROM tbl_franchisee LEFT JOIN tbl_access ON access_franchisee_id = franchisee_object_id LEFT JOIN tbl_admin ON admin_id = access_admin_id
  			WHERE franchisee_deleted IS NULL AND franchisee_published = '1' AND admin_deleted IS NULL AND franchisee_object_id = :franchisee_object_id ";
        $params = array (":franchisee_object_id" => $_franchiseObjId);
        return $DBobject->wrappedSql($sql, $params);
    }
  
  
  
  /**
   * Return array with class-term records for re-enrolment given the term_object_id and class_object_id of the current class
   *
   * @param int $_classTermId
   * @return array
   */
    function GetReEnrolClassTermData($_termObjId, $_classObjId)
    {
        global $DBobject;
  
        $result = array();
        $sql = "SELECT * FROM tbl_term WHERE term_deleted IS NULL AND term_published = '1' AND term_object_id = :id LIMIT 1";
        if ($pterm = $DBobject->wrappedSql($sql, array (":id" => $_termObjId))) {
            $sql = "SELECT * FROM tbl_classterm LEFT JOIN tbl_term ON term_id = classterm_term_id LEFT JOIN tbl_class ON class_object_id = classterm_class_id 
  			WHERE classterm_deleted IS NULL AND classterm_published = '1' AND class_object_id = :class_object_id AND class_deleted IS NULL AND class_published = '1' AND term_deleted IS NULL AND term_start_date > :term_start_date AND term_region = :term_region AND term_published = '1' ORDER BY term_start_date LIMIT 1";
            $params = array ("term_start_date" => $pterm[0]['term_start_date'],":term_region" => $pterm[0]['term_region'], ":class_object_id" => $_classObjId);
            if ($res = $DBobject->wrappedSql($sql, $params)) {
                $result = $res[0];
            }
        }
        return $result;
    }
}
